/**
 * @Package: 	com.group.DAO 
 * @author: 	Liu  
 * @date: 		Dec 27, 2018 9:28:19 AM 
 */
package com.group.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.group.C3P0Utils.C3P0Utils;
import com.group.pojo.GClass;
import com.group.pojo.GGroup;
import com.group.pojo.GStudent;


/**
 * @ClassName:	 GClassDAO.java
 * @Description: 班级DAO
 * @author:		 Liu
 * @date: 		 Dec 27, 2018 9:28:19 AM 
 */
public class GClassDAO {
	/**
	 * @Function: 	 GClassDAO.java
	 * @Description: 获得班级信息列表
	 * @param:		 
	 * @return:		 List<GClass>
	 * @author: 	 Liu
	 * @date:		 Dec 27, 2018 2:37:53 PM 
	 */
	public List<GClass> getGClassList(){
		List <GClass> class_list = new ArrayList<GClass>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM g_class";
		try {
			conn = C3P0Utils.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				GClass GClass = new GClass();
				GClass.setClassID(rs.getString("class_id"));
				GClass.setClassName(rs.getString("class_name"));
				GClass.setClassYear(rs.getInt("class_year"));
				GClass.setClassNum(getGClassNumByID(rs.getString("class_id")));
				class_list.add(GClass);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			C3P0Utils.close(rs, pstmt, conn);
		}
		return class_list;
	}
	
	/**
	 * @Function: 	 GClassDAO.java
	 * @Description: 获取指定班级总人数
	 * @param:		   班级ID
	 * @return:		   班级总人数
	 * @author: 	 Liu
	 * @date:		 Dec 27, 2018 3:16:50 PM 
	 */
	public int getGClassNumByID(String classID) {
		int num = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT COUNT(*) FROM g_student WHERE class_id = ?";
		try {
			conn = C3P0Utils.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, classID);
			rs = pstmt.executeQuery();
			while(rs.next())  num = rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return num;
	}
	
	/**
	 * @Function: 	 GClassDAO.java
	 * @Description: 获取班级学生信息
	 * @param:		   班级ID
	 * @return:		   学生信息列表
	 * @author: 	 Liu
	 * @date:		 Dec 28, 2018 8:55:33 AM 
	 */
	public List<GStudent> getGStudentListByID(String classID, int pageNo, int pageSize){
		//开始和结束位置
		int beginPos = (pageNo - 1) * pageSize;
		//int endPos = beginPos + pageSize;
		
		List <GStudent> student_list = new ArrayList<GStudent>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM g_student NATURAL JOIN g_class NATURAL JOIN g_group WHERE class_id = ? LIMIT ?,?";
		try {
			conn = C3P0Utils.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, classID);
			pstmt.setInt(2, beginPos);
			pstmt.setInt(3, pageSize);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				GStudent student = new GStudent();
				GClass gclass = new GClass(rs.getString("class_id"), rs.getString("class_name"), rs.getInt("class_year"));
				GGroup ggroup = new GGroup(rs.getInt("group_id"), rs.getString("group_name"));
				student.setStudentID(rs.getString("student_id"));
				student.setStudentName(rs.getString("student_name"));
				student.setGclass(gclass);
				student.setGgroup(ggroup);
				student_list.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			C3P0Utils.close(rs, pstmt, conn);
		}
		return student_list;
	}
	
	/**
	 * @Function: 	 GClassDAO.java
	 * @Description: 更新班级信息
	 * @param:		  班级类
	 * @author: 	 Liu
	 * @date:		 Dec 28, 2018 7:32:44 PM 
	 */
	public void updateGClassInfo(GClass gclass) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "UPDATE g_class SET class_year = ?, class_name = ? WHERE class_id = ?";
		try {
			conn = C3P0Utils.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, gclass.getClassYear());
			pstmt.setString(2, gclass.getClassName());
			pstmt.setString(3, gclass.getClassID());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			C3P0Utils.close(rs, pstmt, conn);
		}
	}
	
	/**
	 * @Function: 	 GStudentDAO.java
	 * @Description: 获取学生总数
	 * @param:		 
	 * @return:		   学生总数
	 * @author: 	 Liu
	 * @date:		 Jan 2, 2019 7:16:42 PM 
	 */
	public int getRecordCountByID(String classID) {
		int recordCount = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT COUNT(*) FROM g_student WHERE class_id = ?";
		try {
			conn = C3P0Utils.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, classID);
			rs = pstmt.executeQuery();
			if (rs.next())
				recordCount = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			C3P0Utils.close(rs, pstmt, conn);
		}
		return recordCount;
	}
}
